package com.eascs.banknet.boc.dao.v1;

import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import javax.inject.Inject;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.eascs.app.dao.JdbcSession;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.banknet.boc.vo.boc001.v1.BOC001Element;
import com.eascs.banknet.common.dao.BaseO2oDao;
import com.eascs.banknet.common.utils.IdcardUtils;
import com.eascs.banknet.constant.EAContant;
import com.eascs.banknet.data.dao.DditemDao;
import com.eascs.common.util.StringUtil;

@Repository("boc001V1Dao")
public class BOC001Dao extends BaseO2oDao{
	private static final Logger logger = LoggerFactory.getLogger(BOC001Dao.class);
	
	@Inject
	private JdbcSession crmJdbcSession;
	@Inject
	private DditemDao dditemDao;
	
	private Map<String, Object> cityMap = null;
	private Map<String, Object> provinceMap = null;
	private Map<String, Object> countyMap = null;
	
	//从o2o库获取贷款信息
	public BOC001Element queryData(String startId){
		StringBuffer sql = new StringBuffer();
		sql.append("select t2.bank_applyno 'appno',")//银行申请单号
			//.append(" t2.loanapply_no 'loanapplyNo',")//贷款单号
			.append(" t2.loan_id 'customerId', ")//客户ID
			.append(" IFNULL(t1.instrument_number,0)'lafamano',")//文书编号
			.append(" ROUND(t1.ea_mount, 2) 'lafdramt', ")//申请借款金额
			.append(" t1.loan_term 'loanTerm',")//申请借款期限
			.append(" t1.bank_account 'receivedebitno',")//收款人借记卡卡号
			.append(" t1.bank_account 'lafpdebitno',")//还款人借记卡卡号
			.append(" ROUND(t1.loana_mount * 1.5,2) as 'price',")//资金需求总额
			.append(" ROUND(t1.loana_mount * 1.5 - t1.loana_mount, 2) as 'lafapyam',")//自有资金
			.append(" DATE_FORMAT(ADDDATE(t1.submit_date_credit, INTERVAL t1.loan_term MONTH),'%Y%m%d') as 'condrmondate',")//合同提款截至日期
			.append(" case t1.repay_mentway WHEN 'RMT' THEN '2' WHEN 'AKT' THEN '4' else '3' end as 'lafrtmod' ")//还款方式
			.append("   from o2o_loanapply t1, o2o_loan_user t2")
			.append("   where t1.loanapply_no = t2.loanapply_no  ")
			.append("   and t1.bank_type='BC' and (t1.data_source is null or t1.data_source!='2') ")
			.append(" and t1.wf_type in ('S')")
			.append("   and t1.islock = 'N' and t1.bank_flg = 'N'")
			.append(" and contract_flag = 'Y'")
			.append("   and t1.bank_account is not null")
			.append("   and t2.bank_status in ('01B3','0101','0102','0103') and t2.bank_applyno is not null")
			.append("   and t2.spouse_id is null ")
			.append("   and EXISTS (SELECT 1 FROM o2o_datalist t3 WHERE t3.loanapply_no = t1.loanapply_no AND t3.datalist_code = 'BANKAPPLYDATA' AND t3.is_delete = 'N') ");
			if(StringUtil.hasText(startId)){
				sql.append("   AND t2.bank_applyno > '").append(startId).append("' ");
			}
			sql.append(" ORDER BY t2.bank_applyno LIMIT 1");
		return  super.findEntity(sql.toString(), BOC001Element.class);
	}
	
	//从crm库中查询客户和门店资产信息，并合并贷款信息
	public void joinCrmInfo(BOC001Element loanElement) throws Exception{
		StringBuffer sqlCrm = new StringBuffer();
		sqlCrm.append(" SELECT t1.customer_family_name 'perlstnm',")//客户姓
			.append(" t1.customer_given_name 'perfstnm',")//客户名
			.append(" t1.customer_name 'receiveper',")//收款人
			.append(" t1.customer_name 'receiveper',")//收款人
			.append(" t1.mobile_number 'realpermblno',")//移动电话
			.append(" t1.sex 'sexid',")//性别
			.append("t1.id_type 'certy',")//证件类别
			.append(" t1.id_number 'cerno',")//证件号码
			.append(" t1.postcode 'cmpPstcd',")//单位邮编
			.append(" t2.issuing_authority 'percerop',")//发证机关
			.append(" DATE_FORMAT(t2.license_expiration_date, '%Y%m%d') 'percerdt',")//证件到期日
			.append(" t1.customer_name_pinyin 'otherCustnm',")//拼音
			.append(" case t1.marital_status when 1 then 0 when 2 then 1 when 3 then 6 when 4 then 5 else 7 end 'permrtst',")//婚姻状况
			.append(" SUBSTR(t1.id_number,7,8) as 'cbibthdt',")//出生日期
			.append(" t2.recipient_province 'famAddr3ovs',")//常住地址3(省份)
			.append(" t2.recipient_city 'famAddr2ovs',")//常住地址2(地市)
			.append(" t2.recipient_county 'famAddr1ovs',")//常住地址1(详细地址)
			.append(" t3.shopname 'percmpnm',")//现工作单位
			.append(" DATE_FORMAT(t3.stupdate, '%Y%m%d') 'wkibedat', ")//现工作日期
			.append(" t3.telephone 'cmpTelno', ")//单位电话
			.append(" t3.officeprovince 'cmpAddr3ovs',")//工作单位所在省区
			.append(" t3.officecity 'cmpAddr2ovs', t3.officecounty 'cmpAddr1ovs'")
			.append(" from customer_basic_info t1, customer_expansion_info t2, customer_shop_info t3")
			.append(" where t1.customer_id = t2.customer_id and t1.customer_id =t3.customer_id ")
			.append(" and t3.shoptype = 'MAN' and t1.customer_id = ?");
		BOC001Element customerElement = crmJdbcSession.findEntity(sqlCrm.toString(), BOC001Element.class, loanElement.getCustomerId());
		if(null == customerElement){
			logger.info("BOC001,查询客户信息不存在,customerId:[{}]", loanElement.getCustomerId());
			throw new Exception("BOC001,查询客户信息不存在:customerId:"+loanElement.getCustomerId());
		}
		
		//数据合并
		loanElement.setPerlstnm(customerElement.getPerlstnm());//客户姓
		loanElement.setPerfstnm(customerElement.getPerfstnm());//客户名
		loanElement.setRealpermblno(customerElement.getRealpermblno());//移动电话
		loanElement.setCmpPstcd(customerElement.getCmpPstcd());//工作单位邮编
		loanElement.setCmpTelno(customerElement.getCmpTelno());//单位电话
		loanElement.setReceiveper(customerElement.getReceiveper());//收款人
		loanElement.setSexid(customerElement.getSexid());//性别
		loanElement.setCerty(EAContant.IDTypeMap.get(customerElement.getCerty()));//证件类别
		loanElement.setCerno(customerElement.getCerno());//证件号码
		loanElement.setPercerop(StringUtils.substring(customerElement.getPercerop(), 0, 10));//发证机关
		loanElement.setPercerdt(customerElement.getPercerdt());//证件到期日
		loanElement.setOtherCustnm(customerElement.getOtherCustnm());//拼音
		loanElement.setPermrtst(customerElement.getPermrtst());//婚姻状况
		loanElement.setCbibthdt(customerElement.getCbibthdt());//出生日期
		loanElement.setFamAddr3ovs(customerElement.getFamAddr3ovs());//收件省
		loanElement.setFamAddr2ovs(customerElement.getFamAddr2ovs());//收件市
		loanElement.setFamAddr1ovs(customerElement.getFamAddr1ovs());//收件区
		loanElement.setPercmpnm(customerElement.getPercmpnm());//现工作单位
		loanElement.setWkibedat(customerElement.getWkibedat());//现工作日期
		loanElement.setCmpAddr3ovs(customerElement.getCmpAddr3ovs());//工作单位 省
		loanElement.setCmpAddr2ovs(customerElement.getCmpAddr2ovs());//工作单位 市
		loanElement.setCmpAddr1ovs(customerElement.getCmpAddr1ovs());//工作单位 区
		// 收件省
		if(StringUtils.isBlank(loanElement.getFamAddr3ovs())){
			logger.info("BOC001,收件省为空,customerId:[{}]", loanElement.getCustomerId());
			throw new Exception("BOC001,收件省为空:customerId:"+loanElement.getCustomerId());
		}
		// 收件市
		if(StringUtils.isBlank(loanElement.getFamAddr2ovs())){
			logger.info("BOC001,收件市为空,customerId:[{}]", loanElement.getCustomerId());
			throw new Exception("BOC001,收件市为空:customerId:"+loanElement.getCustomerId());
		}
		// 收件区
//		if(StringUtils.isBlank(loanElement.getFamAddr1ovs())){
//			loanElement.setFamAddr1ovs(loanElement.getFamAddr2ovs());
//			logger.info("BOC001,收件地址区为空,customerId:[{}]", loanElement.getCustomerId());
//			throw new Exception("BOC001,收件地址区为空:customerId:"+loanElement.getCustomerId());
//		}
		// 工作单位省
		if(StringUtils.isBlank(loanElement.getCmpAddr3ovs())){
			logger.info("BOC001,工作单位省为空,customerId:[{}]", loanElement.getCustomerId());
			throw new Exception("BOC001,工作单位省为空:customerId:"+loanElement.getCustomerId());
		}
		// 工作单位市
		if(StringUtils.isBlank(loanElement.getCmpAddr2ovs())){
			logger.info("BOC001,工作单位市为空,customerId:[{}]", loanElement.getCustomerId());
			throw new Exception("BOC001,工作单位市为空:customerId:"+loanElement.getCustomerId());
		}
		// 工作单位地区
//		if(StringUtils.isBlank(loanElement.getCmpAddr1ovs())){
//			loanElement.setCmpAddr1ovs(loanElement.getCmpAddr2ovs());
//			logger.info("BOC001,工作单位地区为空,customerId:[{}]", loanElement.getCustomerId());
//			throw new Exception("BOC001,工作单位地区为空:customerId:"+loanElement.getCustomerId());
//		}
		//性别
		if (!StringUtil.hasText(loanElement.getSexid())) {
			String sexValue = IdcardUtils.getGenderByIdCard(loanElement.getCerno());
			loanElement.setSexid(EAContant.sexMap.get(sexValue));
		} else {
			loanElement.setSexid(EAContant.sexMap.get(loanElement.getSexid()));
		}
		//客户户籍
		String domicile = "";
		// 深圳本地
		if ("4403".equals(customerElement.getFamAddr2ovs())){
			domicile = "4";
		} else {
			if ("44".equals(customerElement.getFamAddr3ovs())){
				domicile = "3";// 本省
			} else {
				domicile = "2";// 外省
			}
		}
		loanElement.setDomicile(domicile);
		if(null == cityMap){
			cityMap = dditemDao.getDdItemByCode("city");
		}
		if(null == provinceMap){
			provinceMap = dditemDao.getDdItemByCode("province");
		}
		if(null == countyMap){
			countyMap = dditemDao.getDdItemByCode("country");
		}
		// 收件省 映射
		String bocFamAddr3ovs = (String)provinceMap.get(loanElement.getFamAddr3ovs());
		if(StringUtils.isBlank(bocFamAddr3ovs)){
			logger.info("BOC001,CRM区域(省)Code:[{}],名称为空,customerId:[{}]", new Object[]{loanElement.getFamAddr3ovs(),loanElement.getCustomerId()});
			throw new Exception("BOC001,CRM区域(省)Code:["+loanElement.getFamAddr3ovs()+"],名称为空,customerId:["+loanElement.getCustomerId()+"]");
		}
		// 收件市 映射
		String bocFamAddr2ovs = (String)cityMap.get(loanElement.getFamAddr2ovs());
		if(StringUtils.isBlank(bocFamAddr2ovs)){
			logger.info("BOC001,CRM区域(市)Code:[{}],名称为空,customerId:[{}]", new Object[]{loanElement.getFamAddr2ovs(),loanElement.getCustomerId()});
			throw new Exception("BOC001,CRM区域(市)Code:["+loanElement.getFamAddr2ovs()+"],名称为空,customerId:["+loanElement.getCustomerId()+"]");
		}
		// 收件区域 映射
		String bocFamAddr1ovs = null;
		if(StringUtils.isBlank(loanElement.getFamAddr1ovs())){
			bocFamAddr1ovs = bocFamAddr2ovs;
		}else{
			bocFamAddr1ovs = (String)countyMap.get(loanElement.getFamAddr1ovs());
		}
		if(StringUtils.isBlank(bocFamAddr1ovs)){
			logger.info("BOC001,CRM区域(区域)Code:[{}],名称为空,customerId:[{}]", new Object[]{loanElement.getFamAddr1ovs(),loanElement.getCustomerId()});
			throw new Exception("BOC001,CRM区域(区域)Code:["+loanElement.getFamAddr1ovs()+"],名称为空,customerId:["+loanElement.getCustomerId()+"]");
		}
		
		// 工作单位省 映射
		String bocCmpAddr3ovs = (String)provinceMap.get(loanElement.getCmpAddr3ovs());
		if(StringUtils.isBlank(bocCmpAddr3ovs)){
			logger.info("BOC001,CRM区域(省)Code:[{}],名称为空,customerId:[{}]", new Object[]{loanElement.getCmpAddr3ovs(),loanElement.getCustomerId()});
			throw new Exception("BOC001,CRM区域(省)Code:["+loanElement.getCmpAddr3ovs()+"],名称为空,customerId:["+loanElement.getCustomerId()+"]");
		}
		// 工作单位市 映射
		String bocCmpAddr2ovs = (String)cityMap.get(loanElement.getCmpAddr2ovs());
		if(StringUtils.isBlank(bocCmpAddr2ovs)){
			logger.info("BOC001,CRM区域(市)Code:[{}],名称为空,customerId:[{}]", new Object[]{loanElement.getCmpAddr2ovs(),loanElement.getCustomerId()});
			throw new Exception("BOC001,CRM区域(市)Code:["+loanElement.getCmpAddr2ovs()+"],名称为空,customerId:["+loanElement.getCustomerId()+"]");
		}
		// 工作单位地区 映射
		String bocCmpAddr1ovs = null;
		if(StringUtils.isBlank(loanElement.getCmpAddr1ovs())){
			bocCmpAddr1ovs = bocCmpAddr2ovs;
		}else{
			bocCmpAddr1ovs = (String)countyMap.get(loanElement.getCmpAddr1ovs());
		}
		if(StringUtils.isBlank(bocCmpAddr1ovs)){
			logger.info("BOC001,CRM区域(区域)Code:[{}],名称为空,customerId:[{}]", new Object[]{loanElement.getCmpAddr1ovs(),loanElement.getCustomerId()});
			throw new Exception("BOC001,CRM区域(区域)Code:["+loanElement.getCmpAddr1ovs()+"],名称为空,customerId:["+loanElement.getCustomerId()+"]");
		}
		
		loanElement.setFamAddr3ovs(bocFamAddr3ovs);//收件省
		loanElement.setFamAddr2ovs(bocFamAddr2ovs);//收件市
		loanElement.setFamAddr1ovs(bocFamAddr1ovs);//收件区
		loanElement.setCmpAddr3ovs(bocCmpAddr3ovs);//工作单位 省
		loanElement.setCmpAddr2ovs(bocCmpAddr2ovs);//工作单位 市
		loanElement.setCmpAddr1ovs(bocCmpAddr1ovs);//工作单位 区
		loanElement.setFamAddr3(bocFamAddr3ovs);//家庭住址 省
		loanElement.setFamAddr2(bocFamAddr2ovs);//家庭住址 市
		loanElement.setFamAddr1(bocFamAddr1ovs);//家庭住址 区
	}
	
	/**
	 * 元素校验
	 * @param loanElement
	 * @return
	 */
	public String checkElement(BOC001Element loanElement){
		List<String> list = new LinkedList<>();
		if(StringUtil.isEmpty(loanElement.getLafpdebitno())){
			list.add("放款款/还款卡号不能为空");
		}
		if(StringUtil.isEmpty(loanElement.getReceivedebitno())){
			list.add("收款人姓名不能为空");
		}
		if(StringUtil.isEmpty(loanElement.getPercerop())){
			list.add("发证机关不能为空");
		}
		if(StringUtil.isEmpty(loanElement.getPercerdt())){
			list.add("证件到期日不能为空");
		}
		if(StringUtil.isEmpty(loanElement.getRealpermblno())){
			list.add("移动电话不能为空");
		}
		if(StringUtil.isEmpty(loanElement.getWkibedat())){
			list.add("门店成立日期(现工作日期)不能为空");
		}
		return StringUtils.join(list,",");
	}

	@Transactional("o2oTransactionManager")
	public void updateInfo(String loanapplyNo, String appno) throws Exception{
		String sql = "UPDATE o2o_loanapply SET bank_flg ='Y' WHERE loanapply_no=? ";
		int rw1 = super.execute(sql, loanapplyNo);
		if(rw1 <= 0 ){
			throw new RuntimeException("贷款申请完成，更新贷款状态失败，loanapplyNo:"+loanapplyNo);
		}
		String sql2 = "UPDATE o2o_loan_user SET bank_status='2' WHERE bank_applyno=? ";
		int rw2 = super.execute(sql2, appno);
		if(rw2 <= 0 ){
			throw new RuntimeException("贷款申请完成，更新银行状态失败，loanapplyNo:"+loanapplyNo);
		}
	}
}
